*********** 질의 최적화 *********** 통계 정보 갱신 ============== **UPDATE STATISTICS ON** 문은 질의 처리기에서 사용되는 내부 통계 정보를 생성한다. 이러한 통계 정보는 데이터베이스 시스템이 질의를 처리하는데 효과적인 방법을 사용할 수 있게 한다. :: UPDATE STATISTICS ON { table_spec [ {, table_spec } ] | ALL CLASSES | CATALOG CLASSES } [ ; ] table_spec ::= single_table_spec | ( single_table_spec [ {, single_table_spec } ] ) single_table_spec ::= [ ONLY ] table_name | ALL table_name [ ( EXCEPT table_name ) ] * **ALL CLASSES** : 키워드 **ALL CLASSES** 를 지정하였을 경우 데이터베이스 안에 존재하는 모든 테이블에 대한 통계 정보가 갱신된다. 통계 정보 갱신 시작과 종료 시 서버 에러 로그에 NOTIFICATION 메시지를 출력하며, 이를 통해 통계 정보 갱신에 걸리는 시간을 확인할 수 있다. :: Time: 05/07/13 15:06:25.052 - NOTIFICATION *** file ../../src/storage/statistics_sr.c, line 123 CODE = -1114 Tran = 1, CLIENT = testhost:csql(21060), EID = 4 Started to update statistics (class "code", oid : 0|522|3). Time: 05/07/13 15:06:25.053 - NOTIFICATION *** file ../../src/storage/statistics_sr.c, line 330 CODE = -1115 Tran = 1, CLIENT = testhost:csql(21060), EID = 5 Finished to update statistics (class "code", oid : 0|522|3, error code : 0). .. note:: 2008 R4.3 이하 및 9.1 버전에서는 인덱스 추가 시 기존의 모든 인덱스의 통계 정보가 갱신되면서 시스템의 부하로 작용했으나, 2008 R4.4, 9.2 버전부터는 추가되는 인덱스의 통계 정보만 갱신된다. 통계 정보 확인 ============== CSQL 인터프리터의 세션 명령어로 지정한 테이블의 통계 정보를 확인한다. :: csql> ;info stats table_name * *table_name* : 통계 정보를 확인할 테이블 이름 다음은 CSQL 인터프리터에서 *t1* 테이블의 통계 정보를 출력하는 예제이다. .. code-block:: sql CREATE TABLE t1 (code INT); INSERT INTO t1 VALUES(1),(2),(3),(4),(5); CREATE INDEX i_t1_code ON t1(code); UPDATE STATISTICS ON t1; :: ;info stats t1 CLASS STATISTICS **************** Class name: t1 Timestamp: Mon Mar 14 16:26:40 2011 Total pages in class heap: 1 Total objects: 5 Number of attributes: 1 Attribute: code id: 0 Type: DB_TYPE_INTEGER Minimum value: 1 Maximum value: 5 B+tree statistics: BTID: { 0 , 1049 } Cardinality: 5 (5) , Total pages: 2 , Leaf pages: 1 , Height: 2 .. _show-query-plan: 질의 실행 계획 보기 =================== CUBRID SQL 질의에 대한 실행 계획(query plan)을 보기 위해서는 다음의 방법을 사용할 수 있다. * CUBRID 매니저 또는 CUBRID 쿼리 브라우저에서 플랜 보기 버튼을 누른다. CUBRID 매니저 또는 CUBRID 쿼리 브라우저의 사용 방법에 대해서는 `CUBRID 매니저 매뉴얼 `_ 또는 `CUBRID 쿼리 브라우저 매뉴얼 `_\ 을 참고한다. .. image:: /images/query_plan_on_CM.png * CSQL 인터프리터에서 ;plan simple 또는 ;plan detail 명령을 실행하거나 **SET OPTIMIZATION** 구문을 이용해서 최적화 수준(optimization level) 값을 변경시킨다. 현재의 최적화 수준 값은 **GET OPTIMIZATION** 구문으로 얻을 수 있다. CSQL 인터프리터에 대한 자세한 내용은 :ref:`csql-session-commands`\ 를 참고한다. **SET OPTIMIZATION** 또는 **GET OPTIMIZATION LEVEL** 구문은 다음과 같다. :: SET OPTIMIZATION LEVEL opt-level [;] GET OPTIMIZATION LEVEL [ { TO | INTO } variable ] [;] * *opt-level* : 최적화 수준을 지정하는 값으로 다음과 같은 의미를 갖는다. * 0 : 질의 최적화를 수행하지 않는다. 실행하는 질의는 가장 단순한 형태의 실행 계획을 가지고 실행된다. 디버깅의 용도 이외에는 사용되지 않는다. * 1 : 질의 최적화를 수행한다. CUBRID에서 사용되는 기본 설정 값으로 대부분의 경우 변경할 필요가 없다. * 2: 질의 최적화를 수행하여 실행 계획을 생성하나 질의 자체는 수행되지 않는다. 일반적으로 사용되지 않고 다음 질의 실행 계획 보기를 위한 설정값과 같이 설정되어 사용된다. * 257 : 질의 최적화를 수행하여 생성된 질의 실행 계획(플랜)을 출력한다. 256+1의 값으로 해석하여 값을 1로 설정하고 질의 실행 계획 출력을 지정한 것과 같다. * 258 : 질의 최적화를 수행하여 생성된 질의 실행 계획을 출력하나 질의를 수행하지는 않는다. 256+2의 값으로 해석하여 2로 설정하고 질의 실행 계획 출력을 지정한 것과 같다. 질의 실행 계획을 살펴보고자 하나 실행 결과에는 관심이 없을 경우 유용한 설정이다. * 513 : 질의 최적화를 수행하고 상세 질의 실행 계획을 출력한다. 512+1의 의미이다. * 514 : 질의 최적화를 수행하고 상세 질의 실행 계획을 출력하나 질의는 실행하지는 않는다. 512+2의 의미이다. .. note:: 2, 258, 514와 같이 질의를 실행하지 않게 최적화 수준을 설정하는 경우 SELECT 문 뿐만 아니라 INSERT, UPDATE, DELETE, REPLACE, TRIGGER, SERIAL 문 등 모든 질의문이 실행되지 않는다. CUBRID 질의 최적화기는 사용자에 의해 설정된 최적화 수준 값을 참조하여 최적화 여부와 질의 실행 계획의 출력 여부를 결정한다. 다음은 CSQL에서 ";plan simple" 명령 입력 또는 "SET OPTIMIZATION LEVEL 257;"을 입력 후 질의를 수행한 결과이다. .. code-block:: sql SET OPTIMIZATION LEVEL 257; -- csql> ;plan simple SELECT /*+ recompile */ DISTINCT h.host_year, o.host_nation FROM history h INNER JOIN olympic o ON h.host_year = o.host_year AND o.host_year > 1950; :: Query plan: Sort(distinct) Nested-loop join(h.host_year=o.host_year) Index scan(olympic o, pk_olympic_host_year, (o.host_year> ?:0 )) Sequential scan(history h) * Sort(distinct): DISTINCT를 수행한다. * Nested-loop join: 조인 방식이 Nested-loop이다. * Index scan: olympic 테이블에 대해 pk_olympic_host_year를 사용하여 index scan. 이때 인덱스를 사용한 조건은 "o.host_year> ?"이다. CSQL에서 ";plan detail" 명령 입력 또는 "SET OPTIMIZATION LEVEL 513;"을 입력 후 질의를 수행하면 상세 내용을 출력한다. .. code-block:: sql SET OPTIMIZATION LEVEL 513; -- csql> ;plan detail SELECT /*+ RECOMPILE */ DISTINCT h.host_year, o.host_nation FROM history h INNER JOIN olympic o ON h.host_year = o.host_year AND o.host_year > 1950; :: Join graph segments (f indicates final): seg[0]: [0] seg[1]: host_year[0] (f) seg[2]: [1] seg[3]: host_nation[1] (f) seg[4]: host_year[1] Join graph nodes: node[0]: history h(147/1) node[1]: olympic o(25/1) (sargs 1) Join graph equivalence classes: eqclass[0]: host_year[0] host_year[1] Join graph edges: term[0]: h.host_year=o.host_year (sel 0.04) (join term) (mergeable) (inner-join) (indexable host_year[1]) (loc 0) Join graph terms: term[1]: o.host_year range (1950 gt_inf max) (sel 0.1) (rank 2) (sarg term) (not-join eligible) (indexable host_year[1]) (loc 0) Query plan: temp(distinct) subplan: nl-join (inner join) edge: term[0] outer: iscan class: o node[1] index: pk_olympic_host_year term[1] cost: 1 card 2 inner: sscan class: h node[0] sargs: term[0] cost: 1 card 147 cost: 3 card 15 cost: 9 card 15 Query stmt: select distinct h.host_year, o.host_nation from history h, olympic o where h.host_year=o.host_year and (o.host_year> ?:0 ) 위의 출력 결과에서 질의 계획과 관련하여 봐야 할 정보는 "Query plan:"이며, 가장 안쪽의 윗줄부터 순서대로 실행된다. 즉, outer: iscan -> inner:scan이 nl-join에서 반복 수행되고, 마지막으로 temp(distinct)가 수행된다. "Join graph segments"는 "Query plan:"에서 필요한 정보를 좀더 확인하는 용도로 사용한다. 예를 들어 "Query plan:"에서 "term[0]"는 "Join graph segments"에서 "term[0]: h.host_year=o.host_year (sel 0.04) (join term) (mergeable) (inner-join) (indexable host_year[1]) (loc 0)"로 표현됨을 확인할 수 있다. 위의 "Query plan:" 각 항목에 대한 설명은 다음과 같다. * temp(distinct): (distinct)는 DISTINCT를 실행함을 의미한다. temp는 실행 결과를 임시 공간에 저장했음을 의미한다. * nl-join: "nl-join"은 조인 방식이 중첩 루프 조인(Nested loop join)임을 의미한다. * (inner join): 조인 종류가 "inner join"임을 의미한다. * outer: iscan: outer 테이블에서는 iscan(index scan)을 수행한다. * class: o node[1]: o라는 테이블을 사용하며 상세 정보는 Join graph segments의 node[1]을 확인한다. * index: pk_olympic_host_year term[1]: pk_olympic_host_year 인덱스를 사용하며 상세 정보는 Join graph segments의 term[1]을 확인한다. * cost: 해당 구문을 수행하는데 드는 비용이다. * card: 카디널리티(cardinality)를 의미한다. * inner: sscan: inner 테이블에 sscan(sequential scan)을 수행한다. * class: h node[0]: h라는 테이블을 사용하며 상세 정보는 Join graph segments의 node[0]을 확인한다. * sargs: term[0]: sargs는 데이터 필터(인덱스를 사용하지 않는 WHERE 조건)를 나타내며, term[0]는 데이터 필터로 사용된 조건을 의미한다. * cost: 해당 구문을 수행하는데 드는 비용이다. * card: 카디널리티(cardinality)를 의미한다. * cost: 전체 구문을 수행하는데 드는 비용이다. 앞서 수행된 모든 비용을 포함한다. * card: cardinality를 뜻한다. **질의 계획 관련 용어** 다음은 질의 계획으로 출력되는 각 용어에 대한 의미를 정리한 것이다. * 조인 방식: 질의 계획에서 출력되는 조인 방식은 위에서 "nl-join" 부분으로 다음과 같다. * nl-join: 중첩 루프 조인, Nested loop join * m-join: 정렬 병합 조인, Sort merge join * idx_join: 중첩 루프 조인인데 outer 테이블의 행(row)을 읽으면서 inner 테이블에서 인덱스를 사용하는 조인 * 조인 종류: 위에서 (inner join) 부분으로, 질의 계획에서 출력되는 조인 종류는 다음과 같다. * inner join * left outer join * right outer join: 질의 계획에서는 질의문의 "outer" 방향과 다른 방향이 출력될 수도 있다. 예를 들어, 질의문에서는 "right outer"로 지정했는데 질의 계획에는 "left outer"로 출력될 수도 있다. * cross join * 조인 테이블의 종류: 위에서 outer/inner 부분으로, 중첩 루프 조인에서 루프의 어느 쪽에 위치하는가를 기준으로 outer 테이블과 inner 테이블로 나뉜다. * outer 테이블: 조인할 때 가장 처음에 읽을 기준 테이블 * inner 테이블: 조인할 때 나중에 읽을 대상 테이블 * 스캔 방식: 위에서 iscan/sscan 부분으로, 해당 질의가 인덱스를 사용하는지 여부를 판단할 수 있다. * sscan: 순차 스캔(sequential scan). 풀 테이블 스캔(full table scan)이라고도 하며 인덱스를 사용하지 않고 테이블 전체를 스캔한다. * iscan: 인덱스 스캔(index scan). 인덱스를 사용하여 스캔할 데이터의 범위를 한정한다. * cost: CPU, IO 등 주로 리소스의 사용과 관련하여 비용을 내부적으로 산정한다. * card: 카디널리티(cardinality)를 의미하며, 선택될 것으로 예측되는 행의 개수이다. 다음은 USE_MERGE 힌트를 명시하여 m-join(정렬 병합 조인, sort merge join)을 수행하는 경우의 예이다. 일반적으로 정렬 병합 조인은 outer 테이블과 inner 테이블을 정렬하여 병합하는 것이 인덱스를 사용하여 중첩 루프 조인(nested loop join)을 수행하는 것보다 유리하다고 판단될 때 사용되며, 조인되는 두 테이블 모두 행의 개수가 매우 많은 경우 유리할 수 있다. 대부분의 경우 정렬 병합 조인을 수행하지 않는 것이 바람직하다. .. code-block:: sql SET OPTIMIZATION LEVEL 513; -- csql> ;plan detail SELECT /*+ RECOMPILE USE_MERGE*/ DISTINCT h.host_year, o.host_nation FROM history h LEFT OUTER JOIN olympic o ON h.host_year = o.host_year AND o.host_year > 1950; :: Query plan: temp(distinct) subplan: temp order: host_year[0] subplan: m-join (left outer join) edge: term[0] outer: temp order: host_year[0] subplan: sscan class: h node[0] cost: 1 card 147 cost: 10 card 147 inner: temp order: host_year[1] subplan: iscan class: o node[1] index: pk_olympic_host_year term[1] cost: 1 card 2 cost: 7 card 2 cost: 18 card 147 cost: 24 card 147 cost: 30 card 147 다음은 idx-join(인덱스 조인, index join)을 수행하는 경우의 예이다. inner 테이블의 조인 조건 칼럼에 인덱스가 있는 경우 inner 테이블의 인덱스를 사용하여 조인을 수행하는 것이 유리하다고 판단되면 **USE_IDX** 힌트를 명시하여 idx-join의 실행을 보장할 수 있다. .. code-block:: sql SET OPTIMIZATION LEVEL 513; -- csql> ;plan detail CREATE INDEX i_history_host_year ON history(host_year); SELECT /*+ RECOMPILE */ DISTINCT h.host_year, o.host_nation FROM history h INNER JOIN olympic o ON h.host_year = o.host_year; :: Query plan: temp(distinct) subplan: idx-join (inner join) outer: sscan class: o node[1] cost: 1 card 25 inner: iscan class: h node[0] index: i_history_host_year term[0] (covers) cost: 1 card 147 cost: 2 card 147 cost: 9 card 147 위의 질의 계획에서 "inner: iscan"의 "index: i_history_host_year term[0]"에 "(covers)"가 출력되는데, 이는 :ref:`covering-index` 기능이 적용된다는 의미이다. 즉, inner 테이블에서 인덱스 내에 필요한 데이터가 있어서 데이터 저장소를 추가로 검색할 필요가 없게 된다. 조인 테이블 중 왼쪽 테이블이 오른쪽 테이블보다 행의 개수가 훨씬 작음을 확신할 때 **ORDERED** 힌트를 명시하여 왼쪽 테이블을 outer 테이블로, 오른쪽 테이블을 inner 테이블로 지정할 수 있다. .. code-block:: sql SELECT /*+ RECOMPILE ORDERED */ DISTINCT h.host_year, o.host_nation FROM history h INNER JOIN olympic o ON h.host_year = o.host_year; .. _query-profiling: 질의 프로파일링 =============== SQL에 대한 성능 분석을 위해서는 질의 프로파일링(profiling) 기능을 사용할 수 있다. 질의 프로파일링을 위해서는 **SET TRACE ON** 구문으로 SQL 트레이스를 설정해야 하며, 프로파일링 결과를 출력하려면 **SHOW TRACE** 구문을 수행해야 한다. 또한 **SHOW TRACE** 결과 출력 시 질의 실행 계획을 항상 포함하려면 /\*+ RECOMPLIE \*/ 힌트를 추가해야 한다. **SET TRACE ON** 구문의 형식은 다음과 같다. :: SET TRACE {ON | OFF} [OUTPUT {TEXT | JSON}] * ON: SQL 트레이스를 on한다. * OFF: SQL 트레이스를 off한다. * OUTPUT TEXT: 일반 TEXT 형식으로 출력한다. OUTPUT 이하 절을 생략하면 TEXT 형식으로 출력한다. * OUTPUT JSON: JSON 형식으로 출력한다. 아래와 같이 **SHOW TRACE** 구문을 실행하면 SQL을 트레이스한 결과를 문자열로 출력한다. :: SHOW TRACE; 다음은 SQL 트레이스를 ON으로 설정하고 질의를 수행한 후, 해당 질의에 대해 트레이스 결과를 출력하는 예이다. :: csql> SET TRACE ON; csql> SELECT /*+ RECOMPILE */ o.host_year, o.host_nation, o.host_city, n.name, SUM(p.gold), SUM(p.silver), SUM(p.bronze) FROM OLYMPIC o, PARTICIPANT p, NATION n WHERE o.host_year = p.host_year AND p.nation_code = n.code AND p.gold > 10 GROUP BY o.host_nation; csql> SHOW TRACE; trace ====================== ' Query Plan: SORT (group by) NESTED LOOPS (inner join) NESTED LOOPS (inner join) TABLE SCAN (o) INDEX SCAN (p.fk_participant_host_year) (key range: (o.host_year=p.host_year)) INDEX SCAN (n.pk_nation_code) (key range: p.nation_code=n.code) rewritten query: select o.host_year, o.host_nation, o.host_city, n.[name], sum(p.gold), sum(p.silver), sum(p.bronze) from OLYMPIC o, PARTICIPANT p, NATION n where (o.host_year=p.host_year and p.nation_code=n.code and (p.gold> ?:0 )) group by o.host_nation Trace Statistics: SELECT (time: 1, fetch: 1059, ioread: 2) SCAN (table: olympic), (heap time: 0, fetch: 26, ioread: 0, readrows: 25, rows: 25) SCAN (index: participant.fk_participant_host_year), (btree time: 1, fetch: 945, ioread: 2, readkeys: 5, filteredkeys: 5, rows: 916) (lookup time: 0, rows: 38) SCAN (index: nation.pk_nation_code), (btree time: 0, fetch: 76, ioread: 0, readkeys: 38, filteredkeys: 38, rows: 38) (lookup time: 0, rows: 38) GROUPBY (time: 0, sort: true, page: 0, ioread: 0, rows: 5) ' 위에서 "Trace Statistics:" 이하가 트레이스 결과를 출력한 것이며, 트레이스 항목에 대한 설명은 다음과 같다. **SELECT** * time: 해당 질의에 대한 전체 수행 시간(ms) * fetch: 해당 질의에 대해 페이지를 fetch한 회수 * ioread: 해당 질의에 대한 전체 I/O 읽기 회수. 데이터를 읽을 때 물리적으로 디스크에 접근한 회수 **SCAN** * heap: 인덱스 없이 데이터를 스캔하는 작업 * time, fetch, ioread: heap에서 해당 연산 수행 시 소요된 시간(ms), fetch 회수, I/O 읽기 회수 * readrows: 해당 연산 수행 시 읽은 행의 개수 * rows: 해당 연산에 대한 결과 행의 개수 * btree: 인덱스 스캔하는 작업 * time, fetch, ioread: btree에서 해당 연산 수행 시 소요된 시간(ms), fetch 회수, I/O 읽기 회수 * readkeys: btree에서 해당 연산 수행 시 읽은 키의 개수 * filteredkeys: 읽은 키 중에 키 필터가 적용된 키의 개수 * rows: 해당 연산에 대한 결과 행의 개수로, 키 필터가 적용된 결과 행의 개수 * lookup: 인덱스 스캔 후 데이터에 접근하는 작업 * time: 해당 연산 수행 시 소요된 시간(ms) * rows: 해당 연산에 대한 결과 행의 개수로, 데이터 필터가 적용된 결과 행의 개수 **GROUPBY** * time: 해당 연산 수행 시 소요된 시간(ms) * sort: 정렬 여부 * page: 정렬에 사용된 임시 페이지 개수로, 내부 정렬 버퍼 외에 사용한 페이지 개수. * rows: 해당 연산에 대한 결과 행의 개수 위의 예는 JSON 형식으로도 출력할 수 있다. :: csql> SET TRACE ON OUTPUT JSON; csql> SELECT n.name, a.name FROM athlete a, nation n WHERE n.code=a.nation_code; csql> SHOW TRACE; trace ====================== '{ "Trace Statistics": { "SELECT": { "time": 29, "fetch": 5836, "ioread": 3, "SCAN": { "access": "temp", "temp": { "time": 5, "fetch": 34, "ioread": 0, "readrows": 6677, "rows": 6677 } }, "MERGELIST": { "outer": { "SELECT": { "time": 0, "fetch": 2, "ioread": 0, "SCAN": { "access": "table (nation)", "heap": { "time": 0, "fetch": 1, "ioread": 0, "readrows": 215, "rows": 215 } }, "ORDERBY": { "time": 0, "sort": true, "page": 21, "ioread": 3 } } } } } } }' CSQL 인터프리터에서 트레이스를 자동으로 설정하는 명령을 사용하면 **SHOW TRACE;** 구문을 별도로 실행하지 않아도 질의 실행 결과를 출력한 후 자동으로 트레이스 결과를 출력한다. CSQL 인터프리터에서 트레이스를 자동으로 설정하는 방법은 :ref:`SQL 트레이스 설정 `\ 을 참고한다. .. note:: * 독립 모드(-S 옵션 사용)로 실행한 CSQL 인터프리터는 SQL 트레이스 기능을 지원하지 않는다. * 여러 개의 SQL을 한 번에 처리하는 경우(batch query, array query) 질의는 프로파일링되지 않는다. .. _sql-hint: SQL 힌트 ======== 사용자는 질의문에 힌트를 주어 해당 질의 성능을 높일 수 있다. 질의 최적화기는 질의문에 대한 최적화 작업을 수행할 때 SQL 힌트를 참고하여 효율적인 실행 계획을 생성한다. CUBRID에서 지원하는 SQL 힌트는 테이블 조인 관련 힌트, 인덱스 관련 힌트, 통계 정보 관련 힌트가 있다. :: { CREATE | ALTER } /*+ NO_STATS */ { TABLE | CLASS } ...; { CREATE | ALTER | DROP } /*+ NO_STATS */ INDEX ...; { SELECT | UPDATE | DELETE } /*+ [ { } ... ] */ ...; MERGE /*+ [ { } ... ] */ INTO ...; ::= USE_NL [ (spec_name_comma_list) ] | USE_IDX [ (spec_name_comma_list) ] | USE_MERGE [ (spec_name_comma_list) ] | ORDERED | USE_DESC_IDX | NO_DESC_IDX | NO_COVERING_IDX | RECOMPILE ::= USE_UPDATE_INDEX () | USE_DELETE_INDEX () | RECOMPILE SQL 힌트는 주석에 더하기 기호(+)를 함께 사용하여 지정한다. 힌트를 사용하는 방법은 :doc:`comment` 절에 소개된 바와 같이 3 가지 방식이 있다. 따라서 SQL 힌트도 다음과 같이 3 가지 방식으로 사용할 수 있다. * /\*+ hint \*/ * --+ hint * //+ hint 힌트 주석은 반드시 키워드 **SELECT**, **CREATE**, **ALTER** 등의 예약어 다음에 나타나야 하고, 더하기 기호(+)가 주석에서 첫 번째 문자로 시작되어야 한다. 여러 개의 힌트를 지정할 때는 공백이 구분자로 사용된다. CREATE/ALTER TABLE 문과 CREATE/ALTER/DROP INDEX 문에는 다음 힌트가 지정될 수 있다. * **NO_STATS** : 통계 정보 관련 힌트로서 해당 DDL 수행 후에 통계 정보를 갱신하지 않는다. 해당 DDL 문의 성능은 향상되나 통계 정보를 갱신하지 않으므로 질의 계획이 최적화되지 않음에 유의한다. SELECT, UPDATE, DELETE 문에는 다음 힌트가 지정될 수 있다. * **USE_NL** : 테이블 조인과 관련한 힌트로서, 질의 최적화기 중첩 루프 조인 실행 계획을 만든다. * **USE_MERGE** : 테이블 조인과 관련한 힌트로서, 질의 최적화기는 정렬 병합 조인 실행 계획을 만든다. * **ORDERED** : 테이블 조인과 관련한 힌트로서, 질의 최적화기는 **FROM** 절에 명시된 테이블의 순서대로 조인하는 실행 계획을 만든다. **FROM** 절에서 왼쪽 테이블은 조인의 외부 테이블이 되고, 오른쪽 테이블은 내부 테이블이 된다. * **USE_IDX** : 인덱스 관련한 힌트로서, 질의 최적화기는 명시된 테이블에 대해 인덱스 조인 실행 계획을 만든다. * **USE_DESC_IDX** : 내림차순 스캔을 위한 힌트이다. 자세한 내용은 :ref:`index-descending-scan` 을 참고한다. * **NO_DESC_IDX** : 내림차순 스캔을 사용하지 않도록 하는 힌트이다. * **NO_COVERING_IDX** : 커버링 인덱스 기능을 사용하지 않도록 하는 힌트이다. 자세한 내용은 :ref:`covering-index` 를 참고한다. * **RECOMPILE** : 질의 실행 계획을 리컴파일한다. 캐시에 저장된 기존 질의 실행 계획을 삭제하고 새로운 질의 실행 계획을 수립하기 위해 이 힌트를 사용한다. .. note:: *spec_name* 이 **USE_NL**, **USE_IDX**, **USE_MERGE** 와 함께 지정될 경우 해당 조인 방법은 *spec_name* 에 대해서만 적용된다. 만약 **USE_NL** 과 **USE_MERGE** 가 함께 지정된 경우 주어진 힌트는 무시된다. 일부 경우에 질의 최적화기는 주어진 힌트에 따라 질의 실행 계획을 만들지 못할 수 있다. 예를 들어 오른쪽 외부 조인에 대해 **USE_NL** 을 지정한 경우 이 질의는 내부적으로 왼쪽 외부 조인 질의로 변환이 되어 조인 순서는 보장되지 않을 수 있다. MERGE 문에는 다음과 같은 힌트를 사용할 수 있다. * **USE_INSERT_INDEX** (<*insert_index_list*>) : MERGE 문의 INSERT 절에서 사용되는 인덱스 힌트. *insert_index_list*\ 에 INSERT 절을 수행할 때 사용할 인덱스 이름을 나열한다. MERGE 문의 <*join_condition*>에 해당 힌트가 적용된다. * **USE_UPDATE_INDEX** (<*update_index_list*>) : MERGE 문의 UPDATE 절에서 사용되는 인덱스 힌트. *update_index_list*\ 에 UPDATE 절을 수행할 때 사용할 인덱스 이름을 나열한다. MERGE 문의 <*join_condition*>과 <*update_condition*>에 해당 힌트가 적용된다. * **RECOMPILE** : 질의 실행 계획을 리컴파일한다. 캐시에 저장된 기존 질의 실행 계획을 삭제하고 새로운 질의 실행 계획을 수립하기 위해 이 힌트를 사용한다. 다음은 심권호 선수가 메달을 획득한 연도와 메달 종류를 구하는 예제이다. 단, *athlete* 테이블을 외부 테이블로 하고 *game* 테이블을 내부 테이블로 하는 중첩 루프 조인 실행 계획을 만들어야 한다. 다음과 같은 질의로 표현이 되는데, 질의최적화기는 *game* 테이블을 외부 테이블로 하고, *athlete* 테이블을 내부 테이블로 하는 중첩 루프 조인 실행 계획을 만든다. .. code-block:: sql SELECT /*+ USE_NL ORDERED */ a.name, b.host_year, b.medal FROM athlete a, game b WHERE a.name = 'Sim Kwon Ho' AND a.code = b.athlete_code; :: name host_year medal ========================================================= 'Sim Kwon Ho' 2000 'G' 'Sim Kwon Ho' 1996 'G' 2 rows selected. 다음은 데이터가 없는 분할 테이블(*before_2008*)의 삭제 성능을 높이기 위해 **NO_STATS** 힌트를 사용하여 질의 실행 시간을 확인하는 예제이다. *participant2* 테이블에는 100만 건 이상의 데이터가 있는 것으로 가정한다. 아래 실행 시간의 차이는 시스템 성능 및 데이터베이스 구성 방법에 따라 다를 수 있다. .. code-block:: sql -- without NO_STATS hint ALTER TABLE participant2 DROP partition before_2008; :: Execute OK. (31.684550 sec) Committed. .. code-block:: sql -- with NO_STATS hint ALTER /*+ NO_STATS */ TABLE participant2 DROP partition before_2008; :: Execute OK. (0.025773 sec) Committed. .. _index-hint-syntax: 인덱스 힌트 =========== 인덱스 힌트 구문은 질의에서 인덱스를 지정할 수 있도록 해서 질의 처리기가 적절한 인덱스를 선택할 수 있게 한다. 이와 같은 인덱스 힌트 구문은 USING INDEX 절을 사용하는 방식과 FROM 절에 { USE | FORCE | IGNORE } INDEX 구문을 사용하는 방식이 있다. USING INDEX ----------- **USING INDEX** 절은 **SELECT**, **DELETE**, **UPDATE** 문의 **WHERE** 절 다음에 지정되어야 한다. **USING INDEX** 절에 강제로 순차 스캔 또는 인덱스 스캔이 사용되게 하거나, 성능에 유리한 인덱스가 포함되도록 한다. **USING INDEX** 절에 인덱스 이름의 리스트가 지정되면 질의 최적화기는 지정된 인덱스만을 대상으로 질의 실행 비용을 계산하고, 지정된 인덱스의 인덱스 스캔 비용과 순차 스캔 비용을 비교하여 최적의 실행 계획을 만든다(CUBRID는 실행 계획을 선택할 때 비용 기반의 질의 최적화를 수행한다). **USING INDEX** 절은 **ORDER BY** 없이 원하는 순서로 결과를 얻고자 할 때 유용하게 사용될 수 있다. CUBRID는 인덱스 스캔을 하면 인덱스에 저장된 순서로 결과가 생성되는데, 한 테이블에 여러 인덱스가 있을 경우 특정 인덱스의 순서로 질의 결과를 얻고자 할 때 **USING INDEX** 를 사용할 수 있다. :: SELECT ... WHERE ... [USING INDEX { NONE | [ ALL EXCEPT ] [ {, } ...] } ] [ ; ] DELETE ... WHERE ... [USING INDEX { NONE | [ ALL EXCEPT ] [ {, } ...] } ] [ ; ] UPDATE ... WHERE ... [USING INDEX { NONE | [ ALL EXCEPT ] [ {, } ...] } ] [ ; ] ::= [table_spec.]index_name [(+) | (-)] | table_spec.NONE * **NONE** : **NONE** 을 지정한 경우 모든 테이블에 대해서 순차 스캔이 사용된다. * **ALL EXCEPT** : 질의 수행 시 지정한 인덱스를 제외한 모든 인덱스가 사용될 수 있다. * *index_name*\ (+) : 인덱스 이름 뒤에 (+)를 지정하면 해당 인덱스 선택이 우선시 된다. 해당 인덱스가 해당 질의를 수행하는데 적합하지 않으면 선택하지 않는다. * *index_name*\ (-) : 인덱스 이름 뒤에 (-)를 지정하면 해당 인덱스가 선택에서 제외된다. * *table_spec*.\ **NONE** : 해당 테이블의 모든 인덱스가 선택에서 제외되어 순차 스캔이 사용된다. USE, FORCE, IGNORE INDEX ------------------------ FROM 절의 테이블 명세 뒤에 **USE**, **FORCE**, **IGNORE INDEX** 구문을 통해서 인덱스 힌트를 지정할 수 있다. :: FROM table_spec [ ] ... ::= { USE | FORCE | IGNORE } INDEX ( [, ...] ) ::= [table_spec.]index_name * **USE INDEX** ( <*index_spec*> ): 지정한 인덱스들만 선택 시에 고려한다. * **FORCE INDEX** ( <*index_spec*> ): 해당 인덱스 선택이 우선시 된다. * **IGNORE INDEX** ( <*index_spec*> ): 지정한 인덱스들은 선택에서 제외된다. USE, FORCE, IGNORE INDEX 구문은 시스템에 의해 자동적으로 적절한 USING INDEX 구문으로 재작성된다. 인덱스 힌트 사용 예 ------------------- .. code-block:: sql CREATE TABLE athlete2 ( code SMALLINT PRIMARY KEY, name VARCHAR(40) NOT NULL, gender CHAR(1), nation_code CHAR(3), event VARCHAR(30) ); CREATE UNIQUE INDEX athlete2_idx1 ON athlete2 (code, nation_code); CREATE INDEX athlete2_idx2 ON athlete2 (gender, nation_code); 아래 2개의 질의는 같은 동작을 수행하며, 지정된 *athlete2_idx2* 인덱스 스캔 비용이 순차 스캔 비용보다 작을 경우 해당 인덱스 스캔을 선택하게 된다. .. code-block:: sql SELECT /*+ RECOMPILE */ * FROM athlete2 USE INDEX (athlete2_idx2) WHERE gender='M' AND nation_code='USA'; SELECT /*+ RECOMPILE */ * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX athlete2_idx2; 아래 2개의 질의는 같은 동작을 수행하며, 항상 *athlete2_idx2*\ 를 사용한다. .. code-block:: sql SELECT /*+ RECOMPILE */ * FROM athlete2 FORCE INDEX (athlete2_idx2) WHERE gender='M' AND nation_code='USA'; SELECT /*+ RECOMPILE */ * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX athlete2_idx2(+); 아래 2개의 질의는 같은 동작을 수행하며, 질의 수행 시 *athlete2_idx2*\ 를 사용하지 않는다. .. code-block:: sql SELECT /*+ RECOMPILE */ * FROM athlete2 IGNORE INDEX (athlete2_idx2) WHERE gender='M' AND nation_code='USA'; SELECT /*+ RECOMPILE */ * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX athlete2_idx2(-); 다음 질의는 수행 시 항상 순차 스캔을 선택한다. .. code-block:: sql SELECT * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX NONE; SELECT * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX athlete2.NONE; 다음 질의는 수행 시 *athlete2_idx2*\ 를 제외한 모든 인덱스의 사용이 가능하도록 한다. .. code-block:: sql SELECT * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX ALL EXCEPT athlete2_idx2; 다음과 같이 **USE INDEX** 구문 또는 **USING INDEX** 구문에서 여러 인덱스를 지정한 경우 질의 최적화기는 지정된 인덱스 중 하나를 선택한다. .. code-block:: sql SELECT * FROM athlete2 USE INDEX (athlete2_idx2, athlete2_idx1) WHERE gender='M' AND nation_code='USA'; SELECT * FROM athlete2 WHERE gender='M' AND nation_code='USA' USING INDEX athlete2_idx2, athlete2_idx1; 여러 개의 테이블에 대해 질의를 수행하는 경우, 한 테이블에서는 특정 인덱스를 사용하여 인덱스 스캔을 하고 다른 테이블에서는 순차 스캔을 하도록 지정할 수 있다. 이러한 질의는 다음과 같은 형태가 된다. .. code-block:: sql SELECT * FROM tab1, tab2 WHERE ... USING INDEX tab1.idx1, tab2.NONE; 인덱스 힌트 구문이 있는 질의를 수행할 때 질의 최적화기는 인덱스가 지정되지 않는 테이블에 대해서는 해당 테이블의 사용 가능한 모든 인덱스를 고려한다. 예를 들어, *tab1* 테이블에는 인덱스 *idx1*, *idx2* 이 있고 *tab2* 테이블에는 인덱스 *idx3*, *idx4*, *idx5* 가 있는 경우, *tab1* 에 대한 인덱스만 지정하고 *tab2* 에 대한 인덱스를 지정하지 않으면 질의 최적화기는 *tab2* 의 인덱스도 고려하여 동작한다. .. code-block:: sql SELECT ... FROM tab1, tab2 USE INDEX (tab1.idx1) WHERE ... ; SELECT ... FROM tab1, tab2 WHERE ... USING INDEX tab1.idx1; 위의 예제의 경우에 테이블 *tab1*\ 의 순차 스캔과 *idx1* 인덱스 스캔을 비교하여 테이블 *tab1*\ 의 스캔 방법을 선택하며, 테이블 *tab2*\ 의 순차 스캔과 *idx3*, *idx4*, *idx5* 인덱스 스캔을 비교하여 테이블 *tab2*\ 의 스캔 방법을 선택하게 된다. .. _tuning-index: 인덱스를 활용한 최적화 ====================== .. _covering-index: 커버링 인덱스 ------------- 질의 수행 시 **SELECT** 리스트, **WHERE**, **HAVING**, **GROUP BY**, **ORDER BY** 절에 있는 모든 칼럼의 데이터를 포함하는 인덱스를 커버링 인덱스(covering index)라고 한다. 커버링 인덱스는 질의 수행 시 인덱스 내에 필요한 모든 데이터를 지니고 있어서 인덱스 페이지만 검색하면 되며, 데이터 저장소를 추가로 검색할 필요가 없어 데이터 저장소 접근을 위한 I/O 비용을 줄일 수 있다. 데이터 검색 속도를 향상시키기 위해 커버링 인덱스로 생성하는 것을 고려할 수 있지만, 인덱스의 크기가 커지면 **INSERT** 와 **DELETE** 작업은 느려질 수 있다는 점을 감안해야 한다. 커버링 인덱스의 적용 여부에 대한 규칙은 다음과 같다. * CUBRID 질의 최적화기는 커버링 인덱스의 적용이 가능하면 이를 가장 먼저 사용한다. * 조인 질의의 경우 인덱스가 **SELECT** 리스트에 있는 테이블의 칼럼을 포함하면, 이 인덱스를 사용한다. * 인덱스를 사용할 수 있는 조건이 아닌 경우 커버링 인덱스를 사용할 수 없다. .. code-block:: sql CREATE TABLE t (col1 INT, col2 INT, col3 INT); CREATE INDEX i_t_col1_col2_col3 ON t (col1,col2,col3); INSERT INTO t VALUES (1,2,3),(4,5,6),(10,8,9); 다음의 예는 **SELECT** 하는 칼럼과 **WHERE** 조건의 칼럼이 모두 인덱스 내에 존재하므로, 해당 인덱스가 커버링 인덱스로 사용된다. .. code-block:: sql -- csql> ;plan simple SELECT * FROM t WHERE col1 < 6; :: Query plan: Index scan(t t, i_t_col1_col2_col3, [(t.col1 range (min inf_lt t.col3))] (covers)) col1 col2 col3 ======================================= 1 2 3 4 5 6 .. warning:: **VARCHAR** 타입의 칼럼에서 값을 가져올 때 커버링 인덱스가 적용되는 경우, 뒤에 따라오는 공백 문자열은 잘리게 된다. 질의 최적화 수행 시 커버링 인덱스가 적용되면 질의 결과 값을 인덱스에서 가져오는데, 인덱스에는 뒤이어 나타나는 공백 문자열을 제거한 채로 값을 저장하기 때문이다. 이러한 현상을 원하지 않는다면 커버링 인덱스 기능을 사용하지 않도록 하는 **NO_COVERING_IDX** 힌트를 사용한다. 이 힌트를 사용하면 결과값을 인덱스 영역이 아닌 데이터 영역에서 가져오도록 한다. 다음은 위의 상황의 자세한 예이다. 먼저 **VARCHAR** 타입의 칼럼을 갖는 테이블을 생성하고, 여기에 시작 문자열의 값이 같고 문자열 뒤에 따르는 공백 문자의 개수가 다른 값을 **INSERT** 한다. 그리고 해당 칼럼에 인덱스를 생성한다. .. code-block:: sql CREATE TABLE tab (c VARCHAR(32)); INSERT INTO tab VALUES ('abcd'), ('abcd '), ('abcd '); CREATE INDEX i_tab_c ON tab (c); 인덱스를 반드시 사용하도록(커버링 인덱스가 적용되도록) 했을 때의 질의 결과는 다음과 같다. .. code-block:: sql -- csql>;plan simple SELECT * FROM tab WHERE c='abcd ' USING INDEX i_tab_c(+); :: Query plan: Index scan(tab tab, i_tab_c, (tab.c='abcd ') (covers)) c ====================== 'abcd' 'abcd' 'abcd' 다음은 인덱스를 사용하지 않도록 했을 때의 질의 결과이다. .. code-block:: sql SELECT * FROM tab WHERE c='abcd ' USING INDEX tab.NONE; :: Query plan: Sequential scan(tab tab) c ====================== 'abcd' 'abcd ' 'abcd ' 위의 두 결과 비교에서 알 수 있듯이, 커버링 인덱스가 적용되면 **VARCHAR** 타입에서는 인덱스로부터 값을 가져오면서 뒤이어 나타나는 공백 문자열이 잘린 채로 나타난다. .. note:: 커버링 인덱스 최적화가 적용될 수 있으면 디스크 입출력을 상당히 줄일 수 있기 때문에 성능 향상을 기대할 수 있다. 하지만 특정한 상황에서 커버링 인덱스 스캔 최적화를 원하지 않는다면, 질의에 **NO_COVERING_IDX** 힌트를 명시하면 된다. 힌트를 지정하는 방법은 :ref:`sql-hint`\ 를 참고하면 된다. .. _order-by-skip-optimization: ORDER BY 절 최적화 ------------------ **ORDER BY** 절에 있는 모든 칼럼을 포함하는 인덱스를 정렬된 인덱스(ordered index)라고 한다. **ORDER BY** 절이 있는 질의를 최적화하면 정렬된 인덱스를 통해 질의 결과를 탐색하므로 별도의 정렬 과정을 거치지 않는다(skip order by). 정렬된 인덱스가 되기 위한 일반적인 조건은 **ORDER BY** 절에 있는 칼럼들이 인덱스의 가장 앞부분에 위치하는 경우이다. .. code-block:: sql SELECT * FROM tab WHERE col1 > 0 ORDER BY col1, col2; * *tab* (*col1*, *col2*) 으로 구성된 인덱스는 정렬된 인덱스이다. * *tab* (*col1*, *col2*, *col3*) 으로 구성된 인덱스도 정렬된 인덱스이다. **ORDER BY** 절에서 참조하지 않는 *col3* 는 *col1*, *col2* 뒤에 오기 때문이다. * *tab* (*col1*) 으로 구성된 인덱스는 정렬된 인덱스가 아니다. * *tab* (*col3*, *col1*, *col2*) 혹은 *tab* (*col1*, *col3*, *col2*)로 구성된 인덱스는 최적화에 사용할 수 없다. 이는 *col3* 가 **ORDER BY** 절의 칼럼들 뒤에 위치하지 않기 때문이다. 인덱스를 구성하는 칼럼이 **ORDER BY** 절에 없더라도 그 칼럼의 조건이 상수일 때는 정렬된 인덱스의 사용이 가능하다. .. code-block:: sql SELECT * FROM tab WHERE col2=val ORDER BY col1,col3; *tab* (*col1*, *col2*, *col3*)로 구성된 인덱스가 존재하고 *tab* (*col1*, *col2*)로 구성된 인덱스는 없이 위의 질의를 수행할 때, 질의 최적화기는 *tab* (*col1*, *col2*, *col3*)로 구성된 인덱스를 정렬된 인덱스로 사용한다. 즉, 인덱스 스캔 시 요구하는 순서대로 결과를 가져오므로, 레코드를 정렬할 필요가 없다. 정렬된 인덱스와 커버링 인덱스를 함께 사용할 수 있으면 커버링 인덱스를 먼저 사용한다. 커버링 인덱스를 사용하면 요청한 데이터의 결과가 인덱스 페이지에 모두 들어 있어 추가적인 데이터를 검색할 필요가 없으며, 이 인덱스가 순서까지 만족한다면, 결과를 정렬할 필요가 없기 때문이다. 질의가 조건을 포함하지 않으며 정렬된 인덱스를 사용할 수 있다면, 인덱스의 첫 번째 칼럼이 **NOT NULL** 조건을 만족한다는 전제 하에서는 정렬된 인덱스가 사용될 것이다. .. code-block:: sql CREATE TABLE tab (i INT, j INT, k INT); CREATE INDEX i_tab_j_k on tab (j, k); INSERT INTO tab VALUES (1,2,3), (6,4,2), (3,4,1), (5,2,1), (1,5,5), (2,6,6), (3,5,4); 다음의 예는 *j*, *k* 칼럼으로 **ORDER BY** 를 수행하므로 *tab* (*j*, *k*)로 구성된 인덱스는 정렬된 인덱스가 되고 별도의 정렬 과정을 거치지 않는다. .. code-block:: sql SELECT i,j,k FROM tab WHERE j > 0 ORDER BY j,k; :: -- the selection from the query plan dump shows that the ordering index i_tab_j_k was used and sorting was not necessary -- (/* --> skip ORDER BY */) Query plan: iscan class: tab node[0] index: i_tab_j_k term[0] sort: 2 asc, 3 asc cost: 1 card 0 Query stmt: select tab.i, tab.j, tab.k from tab tab where ((tab.j> ?:0 )) order by 2, 3 /* ---> skip ORDER BY */ i j k ======================================= 5 2 1 1 2 3 3 4 1 6 4 2 3 5 4 1 5 5 2 6 6 다음의 예는 j, k 칼럼으로 **ORDER BY** 를 수행하며 **SELECT** 하는 칼럼을 모두 포함하는 인덱스가 존재하므로 tab(j,k)로 구성된 인덱스가 커버링 인덱스로서 사용된다. 따라서 인덱스 자체에서 값을 가져오게 되며 별도의 정렬 과정을 거치지 않는다. .. code-block:: sql SELECT /*+ RECOMPILE */ j,k FROM tab WHERE j > 0 ORDER BY j,k; :: -- in this case the index i_tab_j_k is a covering index and also respects the ordering index property. -- Therefore, it is used as a covering index and sorting is not performed. Query plan: iscan class: tab node[0] index: i_tab_j_k term[0] (covers) sort: 1 asc, 2 asc cost: 1 card 0 Query stmt: select tab.j, tab.k from tab tab where ((tab.j> ?:0 )) order by 1, 2 /* ---> skip ORDER BY */ j k ========================== 2 1 2 3 4 1 4 2 5 4 5 5 6 6 다음의 예는 *i* 칼럼 조건이 있으며 *j*, *k* 칼럼으로 **ORDER BY** 를 수행하고, **SELECT** 하는 칼럼이 *i*, *j*, *k* 이므로 *tab* (*i*, *j*, *k*)로 구성된 인덱스가 커버링 인덱스로서 사용된다. 따라서 인덱스 자체에서 값을 가져오게 되지만, **ORDER BY** *j*, *k* 에 대한 별도의 정렬 과정을 거친다. .. code-block:: sql CREATE INDEX i_tab_j_k ON tab (i,j,k); SELECT /*+ RECOMPILE */ i,j,k FROM tab WHERE i > 0 ORDER BY j,k; :: -- since an index on (i,j,k) is now available, it will be used as covering index. However, sorting the results according to -- the ORDER BY clause is needed. Query plan: temp(order by) subplan: iscan class: tab node[0] index: i_tab_i_j_k term[0] (covers) sort: 1 asc, 2 asc, 3 asc cost: 1 card 1 sort: 2 asc, 3 asc cost: 7 card 1 Query stmt: select tab.i, tab.j, tab.k from tab tab where ((tab.i> ?:0 )) order by 2, 3 i j k ======================================= 5 2 1 1 2 3 3 4 1 6 4 2 3 5 4 1 5 5 2 6 6 .. note:: :func:`CAST` 연산자 등을 통해 ORDER BY 절의 칼럼이 타입 변환되더라도, 타입 변환 이전의 정렬 순서와 타입 변환 이후의 정렬 순서가 같다면 ORDER BY 절 최적화가 수행된다. +----------------+----------------+ | 변환 이전 | 변환 이후 | +================+================+ | 수치형 타입 | 수치형 타입 | +----------------+----------------+ | 문자열 타입 | 문자열 타입 | +----------------+----------------+ | DATETIME | TIMESTAMP | +----------------+----------------+ | TIMESTAMP | DATETIME | +----------------+----------------+ | DATETIME | DATE | +----------------+----------------+ | TIMESTAMP | DATE | +----------------+----------------+ | DATE | DATETIME | +----------------+----------------+ .. _index-descending-scan: 내림차순 인덱스 스캔 -------------------- 다음과 같이 내림차순 정렬이 있는 질의를 수행할 때 일반적으로 내림차순 인덱스를 생성하여 인덱스를 사용하도록 하면 별도의 정렬 과정이 필요 없다. .. code-block:: sql SELECT * FROM tab [WHERE ...] ORDER BY a DESC; 그런데 같은 칼럼에 대해 오름차순 인덱스와 내림차순 인덱스를 생성하면 교착 상태(deadlock)의 발생 가능성이 높아진다. 이러한 경우를 줄이기 위해 CUBRID는 별도의 내림차순 인덱스를 생성하지 않아도, 오름차순 인덱스만으로 내림차순 인덱스 스캔을 사용할 수 있다. 사용자는 **USE_DESC_IDX** 힌트를 사용하여 내림차순 스캔을 사용하도록 명시할 수 있다. 이 힌트가 명시되지 않으면 **ORDER BY** 절에 나열된 칼럼이 인덱스를 사용할 수 있다는 전제 조건 하에서 아래의 3가지 질의 실행 계획을 고려할 수 있다. * 순차 스캔 + 내림차순 정렬 * 일반적인 오름차순 스캔 + 내림차순 정렬 * 별도의 정렬 작업이 필요 없는 내림차순 스캔 내림차순 스캔을 위해 **USE_DESC_IDX** 힌트가 생략된다 하더라도 질의 최적화기는 위에서 나열한 3가지 중 제일 마지막 실행 계획을 최적의 계획으로 결정한다. .. note:: **USE_DESC_IDX** 힌트는 조인 질의에 대해서는 지원하지 않는다. .. code-block:: sql CREATE TABLE di (i INT); CREATE INDEX i_di_i on di (i); INSERT INTO di VALUES (5),(3),(1),(4),(3),(5),(2),(5); 다음 예는 **USE_DESC_IDX** 힌트 없이 오름차순 스캔을 통해 질의를 수행한다. .. code-block:: sql -- The query will be executed with an ascending scan. SELECT * FROM di WHERE i > 0 LIMIT 3; :: Query plan: Index scan(di di, i_di_i, (di.i range (0 gt_inf max) and inst_num() range (min inf_le 3)) (covers)) i ============= 1 2 3 위의 질의에 **USE_DESC_IDX** 힌트를 추가하면 내림차순 스캔을 통해 다른 결과가 나온다. .. code-block:: sql -- We now run the same query, using the 'use_desc_idx' SQL hint: SELECT /*+ USE_DESC_IDX */ * FROM di WHERE i > 0 LIMIT 3; :: Query plan: Index scan(di di, i_di_i, (di.i range (0 gt_inf max) and inst_num() range (min inf_le 3)) (covers) (desc_index)) i ============= 5 5 5 다음 예는 **ORDER BY** 절을 통해 내림차순 정렬이 요구되는 경우이다. 이 경우 **USE_DESC_IDX** 힌트가 없지만 내림차순 스캔하게 된다. .. code-block:: sql -- We also run the same query, this time asking that the results are displayed in descending order. -- However, no hint is given. -- Since ORDER BY...DESC clause exists, CUBRID will use descending scan, even though the hint is not given, -- thus avoiding to sort the records. SELECT * FROM di WHERE i > 0 ORDER BY i DESC LIMIT 3; :: Query plan: Index scan(di di, i_di_i, (di.i range (0 gt_inf max)) (covers) (desc_index)) i ============= 5 5 5 .. _group-by-skip-optimization: GROUP BY 절 최적화 ------------------ **GROUP BY** 절에 있는 모든 칼럼이 인덱스에 포함되어 질의 수행 시 인덱스를 사용할 수 있어 별도의 정렬 작업을 하지 않는 것을 **GROUP BY** 절 최적화라고 한다. 이를 위해서는 **GROUP BY** 절에 있는 칼럼들이 인덱스를 구성하는 칼럼들의 제일 앞 쪽에 모두 존재해야 한다. .. code-block:: sql SELECT * FROM tab WHERE col1 > 0 GROUP BY col1,col2; * *tab* (*col1*, *col2*)로 구성된 인덱스는 최적화에 사용할 수 있다. * *tab* (*col1*, *col2*, *col3*)로 구성된 인덱스도 사용될 수 있는데, **GROUP BY** 절에서 참조하지 않는 *col3* 는 *col1*, *col2* 뒤에 오기 때문이다. * *tab* (*col1*)로 구성된 인덱스는 최적화에 사용할 수 없다. * *tab* (*col3*, *col1*, *col2*) 혹은 *tab* (*col1*, *col3*, *col2*)로 구성된 인덱스도 최적화에 사용할 수 없는데, *col3* 가 **GROUP BY** 절의 칼럼들 뒤에 위치하지 않기 때문이다. 인덱스를 구성하는 칼럼이 **GROUP BY** 절에 없더라도 그 칼럼의 조건이 상수일 때는 인덱스를 사용할 수 있다. .. code-block:: sql SELECT * FROM tab WHERE col2=val GROUP BY col1,col3; 위의 예에서 *tab* (*col1*, *col2*, *col3*)로 구성된 인덱스가 있으면 이 인덱스를 **GROUP BY** 최적화에 사용한다. 이 경우에도 인덱스 스캔 시 요구하는 순서대로 결과를 가져오므로, **GROUP BY** 에 의해서 행에 대한 정렬이 불필요하게 된다. **WHERE** 절이 없어도 **GROUP BY** 칼럼으로 구성된 인덱스가 있고 그 인덱스의 첫번째 칼럼이 **NOT NULL** 이면 **GROUP BY** 최적화가 적용된다. 집계 함수 사용 시 **GROUP BY** 최적화가 적용되는 경우는 **MIN** ()이나 **MAX** ()를 사용할 때뿐이며, 두 집계 함수가 같이 쓰이려면 같은 칼럼을 사용하는 경우에만 적용된다. .. code-block:: sql CREATE INDEX i_T_a_b_c ON T(a, b, c); SELECT a, MIN(b), c, MAX(b) FROM T WHERE a > 18 GROUP BY a, b; **예제** .. code-block:: sql CREATE TABLE tab (i INT, j INT, k INT); CREATE INDEX i_tab_j_k ON tab (j, k); INSERT INTO tab VALUES (1,2,3), (6,4,2), (3,4,1), (5,2,1), (1,5,5), (2,6,6), (3,5,4); 다음의 예는 *j*, *k* 칼럼으로 **GROUP BY** 를 수행하므로 *tab* (*j*, *k*)로 구성된 인덱스가 사용되고 별도의 정렬 과정이 필요 없다. .. code-block:: sql SELECT i,j,k FROM tab WHERE j > 0 GROUP BY j,k; -- the selection from the query plan dump shows that the index i_tab_j_k was used and sorting was not necessary -- (/* ---> skip GROUP BY */) :: Query plan: iscan class: tab node[0] index: i_tab_j_k term[0] sort: 2 asc, 3 asc cost: 1 card 0 Query stmt: select tab.i, tab.j, tab.k from tab tab where ((tab.j> ?:0 )) group by tab.j, tab.k /* ---> skip GROUP BY */ i j k 5 2 1 1 2 3 3 4 1 6 4 2 3 5 4 1 5 5 2 6 6 다음의 예는 *j*, *k* 칼럼으로 **GROUP BY** 를 수행하며 *j* 에 대한 조건이 없지만 *j* 칼럼은 **NOT NULL** 속성을 지니므로, *tab* (*j*, *k*)로 구성된 인덱스가 사용되고 별도의 정렬 과정이 필요 없다. .. code-block:: sql ALTER TABLE tab CHANGE COLUMN j j INT NOT NULL; SELECT * FROM tab GROUP BY j,k; :: -- the selection from the query plan dump shows that the index i_tab_j_k was used (since j has the NOT NULL constraint ) -- and sorting was not necessary (/* ---> skip GROUP BY */) Query plan: iscan class: tab node[0] index: i_tab_j_k sort: 2 asc, 3 asc cost: 1 card 0 Query stmt: select tab.i, tab.j, tab.k from tab tab group by tab.j, tab.k /* ---> skip GROUP BY */ === === i j k ======================================= 5 2 1 1 2 3 3 4 1 6 4 2 3 5 4 1 5 5 2 6 6